﻿using Dapper;
using DBLibrary.DataBase;
using DBLibray.SysLog;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DBLibrary.DataBase
{
    public class DB : IDisposable
    {
        public static ConcurrentQueue<string> SqlQueue1 = new ConcurrentQueue<string>();
        public static ConcurrentQueue<string> SqlQueue2 = new ConcurrentQueue<string>();
        public static ConcurrentQueue<string> SqlQueue3 = new ConcurrentQueue<string>();

        //private static Lazy<DB> _instance = new Lazy<DB>(() => new DB(dbconstr));
        //public static DB Intance => new DB(dbconstr); //_instance.Value;
        private static readonly string dbType = "SqlServer";


        private string dbconstr;


        private static readonly int dbThreadCount = 5;
        private static readonly int batchMsgSqlCount = 5;

        //IDbConnection db1, db2, db3;                //数据库连接
        string selid1 = "", selid2 = "", selid3 = "";     //id查询字符串

        public string DbType { get; set; }


        private string _db1Sqls = "";

        private string _bulkAddOriginalDataSql ="";

        public string _db2Sqls = "";

        public string _db3Sqls = "";

        private static readonly Random _random = new Random();


        public DB(string constr)          //数据配置选择
        {
            dbconstr = constr;
            DbType = dbType;
            if (dbconstr != "")
            {
                //db1 = new SqlConnection(dbconstr);
                selid1 = ";select SCOPE_IDENTITY();";
            }
        }

        #region SQL Server数据库操作

        private IDbConnection GetConnection()
        {
            IDbConnection db = null;
            if (dbType == "SqlServer")
            {
                if (dbconstr != "")
                {
                    db = new SqlConnection(dbconstr);
                }
            }
            else if (dbType == "MySql")
            {
                if (dbconstr != "")
                {
                    db = new MySqlConnection(dbconstr);
                }
            }
            return db;
        }
        public List<T> QueryList<T>(string cmdstr) where T : class
        {
            try
            {
                using (IDbConnection db = (dbType == "SqlServer")
                      ? (IDbConnection)new SqlConnection(dbconstr)
                      : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    return db.Query<T>(cmdstr).AsList();
                }
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                if(cmdstr.IndexOf("ddmm_history")<0)
                    we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                return null;
            }
        }
        public T Query<T>(string cmdstr) where T : class
        {
            try
            {
                using (IDbConnection db = (dbType == "SqlServer")
                      ? (IDbConnection)new SqlConnection(dbconstr)
                      : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    return (T)db.Query<T>(cmdstr);
                }
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                return null;
            }
        }
        public T GetSingle<T>(string cmdstr) where T : class
        {
            try
            {
                using (IDbConnection db = (dbType == "SqlServer")
                      ? (IDbConnection)new SqlConnection(dbconstr)
                      : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    var r= (T)db.QuerySingle<T>(cmdstr);
                    return r;
                }
            }
            catch (Exception e)
            {
                
                WriteException we = new WriteException();
                if (cmdstr.IndexOf("ddmm_history") < 0)
                    we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                return null;
            }
        }
        /// <summary>
        /// 获取列表，先在缓存中查找，如果没有再去查询数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdstr"></param>
        /// <returns></returns>
        public List<T> QueryCacheList<T>(string key, string cmdstr) where T : class
        {
            try
            {
                List<T> dt = LocalCache.Get(key, () => DataListWorker<T>(cmdstr), 7200);
                return dt;
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                return null;
            }
        }
        /// <summary>
        /// 获取单记录，先在缓存中查找，如果没有再去查询数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdstr"></param>
        /// <returns></returns>
        public T QueryCache<T>(string key, string cmdstr) where T : class
        {
            T dt = LocalCache.Get(key, () => DataWorker<T>(cmdstr), 1);
            return dt;
        }
        /// <summary>
        /// 查询数据库，返回列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdstr"></param>
        /// <returns></returns>
        public List<T> DataListWorker<T>(string cmdstr) where T : class
        {
            List<T> dt = new List<T>();
            try
            {

                using (IDbConnection db = (dbType == "SqlServer")
                     ? (IDbConnection)new SqlConnection(dbconstr)
                     : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    dt = db.Query<T>(cmdstr).AsList();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                dt = null;
            }
            return dt;
        }
        public List<T> GetListFromStoreProcedure<T>(string procName, DateTime startDate, DateTime endDate, string sensorId, int granularity, int sensorType) where T : class
        {
            List<T> dt = new List<T>();
            try
            {

                using (IDbConnection db = (dbType == "SqlServer")
                      ? (IDbConnection)new SqlConnection(dbconstr)
                      : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    dt = db.Query<T>(procName, new
                    {
                        StartDate = startDate, //开始日期
                        EndDate = endDate, //结束日期
                        SensorId = sensorId,// 传感器编号
                        granularity = granularity,//时间粒度：13 每小时，15每十分钟，16每分钟，19每秒
                        sensorType = sensorType
                    }, commandType: CommandType.StoredProcedure).AsList();

                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Database Error;" + e.ToString() + "\n数据：cmdstr:" + procName);
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + procName);
                dt = null;
            }
            return dt;
        }
        void DeriveParameters(SqlCommand sqlCommand)
        {
            try
            {
                sqlCommand.Connection.Open();
                SqlCommandBuilder.DeriveParameters(sqlCommand);
                sqlCommand.Connection.Close();
            }
            catch
            {
                if (sqlCommand.Connection != null)
                {
                    sqlCommand.Connection.Close();
                }
                throw;
            }
        }
        private void AssignParameterValues(SqlCommand sqlCommand, params object[] paraValues)
        {
            if (paraValues != null)
            {
                if ((sqlCommand.Parameters.Count - 1) != paraValues.Length)
                {
                    throw new ArgumentNullException("The number of parameters does not match number of values for stored procedure.");
                }
                for (int i = 0; i < paraValues.Length; i++)
                {
                    sqlCommand.Parameters[i + 1].Value = (paraValues[i] == null) ? DBNull.Value : paraValues[i];
                }
            }
        }
        /// <summary>
        /// 查询数据库，返回单记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdstr"></param>
        /// <returns></returns>
        public T DataWorker<T>(string cmdstr) where T : class
        {
            T data = null;
            try
            {

                using (IDbConnection db = (dbType == "SqlServer")
                       ? (IDbConnection)new SqlConnection(dbconstr)
                       : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    var dt = db.Query<T>(cmdstr);
                    if (dt != null && dt.AsList().Count > 0)
                        data = dt.AsList<T>()[0];
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                data = null;
            }
            return data;
        }
        public DataTable DataWorke(string cmdstr)
        {
            DataTable dt = new DataTable();
            try
            {

                using (IDbConnection db = (dbType == "SqlServer")
                      ? (IDbConnection)new SqlConnection(dbconstr)
                      : (IDbConnection)new MySqlConnection(dbconstr))
                using (var idt = db.ExecuteReader(cmdstr))
                {
                    dt.Load(idt);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                WriteException we = new WriteException();
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
            }
            return dt;
        }
        /// <summary>
        /// 建表
        /// </summary>
        /// <param name="cmdstr"></param>
        /// <returns></returns>
        public string SqDDLlExecute(string cmdstr)
        {
            string i = "1";
            try
            {
                using (IDbConnection db = (dbType == "SqlServer")
                       ? (IDbConnection)new SqlConnection(dbconstr)
                       : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    object o = db.ExecuteScalar(cmdstr);
                    if(o != null)
                    {
                        i = o.ToString();
                    }
                   else
                    { i = "0"; }
                }
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                //Console.WriteLine("HHHH" + cmdstr);
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
                i = "0";
            }
            return i;
        }

        public int SqlExecute(string cmdstr, bool isAddOriginalDataSql = false)
        {
            if (cmdstr == string.Empty)
                return 0;
            int i = 0;
            try
            {
                using (IDbConnection db = (dbType == "SqlServer")
                        ? (IDbConnection)new SqlConnection(dbconstr)
                        : (IDbConnection)new MySqlConnection(dbconstr))
                {
                    i = db.Execute(cmdstr);
                }

                //Task.Run(() =>
                //{
                //    i = db1.Execute(cmdstr);
                //});

            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                //Console.WriteLine("HHHH" + cmdstr);
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
            }
            return i;
        }




       

        public void ClusterEqueue()
        {
            var strSql1 = _db1Sqls.ToString();
            var strSql2 = _db2Sqls.ToString();
            var strSql3 = _db3Sqls.ToString();



            if (string.IsNullOrWhiteSpace(strSql1) || strSql1 == ";") return;

            strSql1 += _bulkAddOriginalDataSql.ToString() + ";";

            // sql1插入事务，减少事务开销
            //var rid = _random.Next(int.MaxValue);
            //if (dbType == "SqlServer")
            //{
            //    strSql1 = $"BEGIN TRAN " +
            //               $"DECLARE @TRAN_ERROR{rid} INT; " +
            //               $"SET @TRAN_ERROR{rid} = 0; " +
            //               $"BEGIN TRY " +
            //               $"   {strSql1} " +
            //               @"END TRY
            //                    BEGIN CATCH
            //                      SET @TRAN_ERROR" + rid + " = @TRAN_ERROR" + rid + @" + 1
            //                    END CATCH
            //                IF(@TRAN_ERROR" + rid + @" > 0)
            //                    BEGIN
            //                        ROLLBACK TRAN;
            //                    END
            //                ELSE
            //                    BEGIN
            //                        COMMIT TRAN;
            //                    END";
            //}
            //else if (dbType == "MySql")
            //{
            //    strSql1 = @"START TRANSACTION;  " +
            //              $"    {strSql1}   " +
            //              @"COMMIT; ";
            //}

            try
            {
                if (!string.IsNullOrWhiteSpace(dbconstr) && !string.IsNullOrWhiteSpace(strSql1))
                    SqlQueue1.Enqueue(strSql1);  // if (db1 != null && !string.IsNullOrWhiteSpace(strSql1))  db1.Execute(strSql1,
                                                 //
                                                 // Timeout: 60 * 1000);
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                //Console.WriteLine("HHHH" + strSql1);
                we.SavaException("Database Error;" + e.ToString() + "\n数据：strSql1:" + strSql1 + "\nstrSql2" + strSql2 +
                                 "\nstrSql3" + strSql3);
            }
            finally
            {
                _db1Sqls = null;
            }
        }

        //private static volatile int _gcClearIntervalCount;

        //public static void ExecuteQueue1()
        //{
        //    List<Task> parallelList = new List<Task>(); // 批量线程
        //    List<string> batchMsgSqls = new List<string>(); // 每个线程同时处理报文数


        //    while (true)
        //    {
        //        string strSb = null;
        //        if (!SqlQueue1.TryDequeue(out strSb))
        //        {
        //            if (batchMsgSqls.Count > 0)
        //            {
        //                string[] sqlArr = new string[batchMsgSqls.Count];
        //                batchMsgSqls.CopyTo(sqlArr);
        //                batchMsgSqls.Clear();

        //                parallelList.Add(
        //                    Task.Run(() =>
        //                    {
        //                        using (IDbConnection db = (dbType == "SqlServer")
        //                            ? (IDbConnection)new SqlConnection(dbconstr)
        //                            : (IDbConnection)new MySqlConnection(dbconstr))
        //                        {
        //                            //var sql = String.Join(";", sqlArr); // 如果这样会入库不完整
        //                            foreach (var sql in sqlArr)
        //                            {
        //                                db.Execute(sql);
        //                            }
        //                        }
        //                    }));
        //            }

        //            if (parallelList.Count > 0)
        //            {
        //                Task[] taskArr = new Task[parallelList.Count];
        //                parallelList.CopyTo(taskArr);
        //                parallelList.Clear();
        //                Task.WaitAny(taskArr);
        //            }
        //            else
        //            {
        //                Thread.Sleep(50);
        //            }
        //            continue;
        //        }

        //        //if (string.IsNullOrWhiteSpace(strSb.Value)) continue;

        //        try
        //        {
        //            batchMsgSqls.Add(strSb);

        //            if (batchMsgSqls.Count == batchMsgSqlCount)
        //            {
        //                string[] sqlArr = new string[batchMsgSqls.Count];
        //                batchMsgSqls.CopyTo(sqlArr);
        //                batchMsgSqls.Clear();
        //                parallelList.Add(
        //                    Task.Run(() =>
        //                    {
        //                        using (IDbConnection db = (dbType == "SqlServer")
        //                            ? (IDbConnection)new SqlConnection(dbconstr)
        //                            : (IDbConnection)new MySqlConnection(dbconstr))
        //                        {
        //                            //var sql = String.Join(";", sqlArr); // 如果这样会入库不完整
        //                            foreach (var sql in sqlArr)
        //                            {
        //                                db.Execute(sql);
        //                            }
        //                        }
        //                    }));
        //            }

        //            if (parallelList.Count == dbThreadCount)
        //            {
        //                Task[] taskArr = new Task[parallelList.Count];
        //                parallelList.CopyTo(taskArr);
        //                parallelList.Clear();
        //                Task.WaitAny(taskArr);
        //            }
        //        }
        //        catch (Exception e)
        //        {
        //            WriteException we = new WriteException();
        //            //Console.WriteLine("HHHH" + strSb);
        //            we.SavaException("Database Error;" + e.ToString() + " \n " + strSb);
        //        }

        //        // 垃圾回收
        //        //Interlocked.Increment(ref _gcClearIntervalCount);
        //        //if (30 == _gcClearIntervalCount)
        //        //{
        //        //    Interlocked.Exchange(ref _gcClearIntervalCount, 0);
        //        //    GC.Collect();
        //        //}
        //    }
        //}

        public int SqlExecuteScalar(string cmdstr)
        {
            if (selid1 == "") return 0;
            int i = 0;
            DataTable dt = new DataTable();

            // cmdstr += selid1;
            try
            {
                using (var db1 = this.GetConnection())
                    dt.Load(db1.ExecuteReader(cmdstr));
            }
            catch (Exception e)
            {
                WriteException we = new WriteException();
                //Console.WriteLine("HHHH" + cmdstr);
                we.SavaException("Database Error;" + e.ToString() + "\n数据：cmdstr:" + cmdstr);
            }
            if (dt != null && dt.Rows.Count > 0 && dt.Rows[0][0] != DBNull.Value)
                i = Convert.ToInt32(dt.Rows[0][0]);
            return i;
        }


        #region IDisposable Support
        private bool disposedValue = false; // 要检测冗余调用

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    _db1Sqls = null;
                    _db2Sqls = null;
                    _db3Sqls = null;
                }

                disposedValue = true;
            }
        }

        ~DB()
        {
            // 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
            Dispose(false);
        }

        // 添加此代码以正确实现可处置模式。
        public void Dispose()
        {
            // 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
        #endregion
    }
}
